package com.java.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class FileDao {

	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl2";
		Connection con = DriverManager.getConnection(url, "scott", "tiger");
		return con;
	}

	private void free(Connection con, Statement st, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public FileInfo getFile(int did) {
		FileInfo fi = new FileInfo();
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		
		try {
			con = getConnection();
			st = con.createStatement();
			String sql = "Update data_info set download_count = download_count+1 where did="
					+ did;
			st.executeUpdate(sql);
			sql = "select title,path from data_info where did=" + did;
			rs = st.executeQuery(sql);
			if (rs.next()) {
				fi.setTitle(rs.getString(1));
				fi.setPath(rs.getString(2));
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			free(con, st, rs);
		}
		return fi;
	}

	public void uploadFile(int tid, int uid, Date share_time, String path,
			String title, String description, double size) {
		Connection con = null;
		Statement st = null;

		try {
			con = getConnection();
			st = con.createStatement();
			String sql = "insert into data_info (tid,uid,share_time,path,title,descrioption,size) values ("
					+ tid
					+ ","
					+ uid
					+ ",'"
					+ share_time.toString()
					+ "','"
					+ path
					+ "','"
					+ title
					+ "','"
					+ description
					+ "',"
					+ size
					+ ")";
			st.executeUpdate(sql);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			free(con, st, null);
		}
	}

	public boolean checkUser(int uid, int did) {
		boolean b = false;
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		int score1 = 0;
		int score2 = 0;

		try {
			con = getConnection();
			st = con.createStatement();
			String sql = "select score from data_info where did=" + did;
			rs = st.executeQuery(sql);
			if (rs.next()) {
				score1 = rs.getInt(1);
			}
			sql = "select score from user where uid=" + uid;
			rs = st.executeQuery(sql);
			if (rs.next()) {
				score2 = rs.getInt(1);
			}
			if (score1 <= score2) {
				b = true;
				sql="update user set score = score-"+score1+" where uid="+uid;
				st.executeUpdate(sql);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			free(con, st, rs);
		}
		return b;
	}
	
	public String getId(String tname){
		String tid=null;
		Connection con=null;
		Statement st=null;
		ResultSet rs=null;
		
		try {
			con=getConnection();
			st=con.createStatement();
			String sql="select tid from type where tname='"+tname+"'";
			rs=st.executeQuery(sql);
			if(rs.next()){
				tid=rs.getString(1);
			}			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			free(con,st,rs);
		}
		
		return tid;
	}

}